drop function if exists pl_select.listar_estcta(in p_mhresum character);
create or replace function pl_select.listar_estcta(in p_mhresum character)
	returns table(
		cidpred character(10),
		ctiping character(10),
		ctiprec character(10),
		ninsolu numeric(18, 2),
		ncancel numeric(18, 2),
		cperanio character(4),
		ntipo integer,
		mpredio character(10),
		dfecadq date,
		dfecdes date,
		dfecven date,
		cmescuo character(4)
	)
as
$body$
	select ax.cidpred
		, ax.ctiping
		, ax.ctiprec
		, case when ax.cmescuo='01' 
			then trunc(trunc(ax.ninsolu / ax.nnrocuo, 2) - (ax.ninsolu - (trunc(ax.ninsolu / ax.nnrocuo, 2) * ax.nnrocuo)), 2)
			else trunc(ax.ninsolu / ax.nnrocuo, 2) end ninsolu
		, 0.00 ncancel
		, ax.cperiod
		, ax.ntipo
		, ax.mpredio
		, ax.dfecadq
		, ax.dfecdes
		, ax.dfecven
		, ax.cmescuo
	from (
		select a.mpredio cidpred
			, a.ctiping
			, a.ctiprec
			, a.ninsolu
			, c.nnrocuo
			, a.cperiod
			, 1 ntipo
			, a.mpredio
			, a.dfecadq
			, a.dfecdes
			, c.cperiod cmescuo
			, c.dfecven::date dfecven
		from (
			select x.mpredio cidpred, '0000000278' ctiping, '0000000279' ctiprec, z.nbarrid ninsolu, z.cperiod, 1 ntipo, x.mpredio, x.dfecadq::date, case when x.nestado='0' then x.dfecdes::date else null end dfecdes
			from registro.dpredio x inner join registro.vpredio z on x.idsigma=z.dpredio and x.mhresum=p_mhresum and z.cperiod::int >= to_char(x.dfecadq, 'yyyy')::int
			union all
			select x.mpredio cidpred, '0000000278' ctiping, '0000000283' ctiprec, z.nparque ninsolu, z.cperiod, 1 ntipo, x.mpredio, x.dfecadq::date, case when x.nestado='0' then x.dfecdes::date else null end dfecdes
			from registro.dpredio x inner join registro.vpredio z on x.idsigma=z.dpredio and x.mhresum=p_mhresum and z.cperiod::int >= to_char(x.dfecadq, 'yyyy')::int
			union all
			select x.mpredio cidpred, '0000000278' ctiping, '0000007267' ctiprec, z.nrecole ninsolu, z.cperiod, 1 ntipo, x.mpredio, x.dfecadq::date, case when x.nestado='0' then x.dfecdes::date else null end dfecdes
			from registro.dpredio x inner join registro.vpredio z on x.idsigma=z.dpredio and x.mhresum=p_mhresum and z.cperiod::int >= to_char(x.dfecadq, 'yyyy')::int
			union all
			select x.mpredio cidpred, '0000000278' ctiping, '0000008509' ctiprec, z.nserena ninsolu, z.cperiod, 1 ntipo, x.mpredio, x.dfecadq::date, case when x.nestado='0' then x.dfecdes::date else null end dfecdes
			from registro.dpredio x inner join registro.vpredio z on x.idsigma=z.dpredio and x.mhresum=p_mhresum and z.cperiod::int >= to_char(x.dfecadq, 'yyyy')::int
			union all
			select null cidpred, '0000000273' ctiping, '0000000273' ctiprec, x.nimpanu ninsolu, x.cperiod, 1 ntipo, null mpredio, null dfecadq, null dfecdes
			from registro.dresumn x where x.mhresum=p_mhresum	
		) a
		inner join registro.pestcta c on a.ctiprec::int=c.ctiprec::int and a.cperiod::int=c.cperanio::int 
	) ax
	where (
		ax.ctiprec='0000000273' or
		ax.cperiod::int > to_char(ax.dfecadq, 'yyyy')::int or (
			ax.cperiod::int = to_char(ax.dfecadq, 'yyyy')::int and 
			ax.cmescuo::int > to_char(ax.dfecadq, 'MM')::int
		)
	) and ax.ninsolu > 0;
$body$
language sql volatile cost 100 rows 1000;

drop function if exists pl_select.listar_estcta(in p_mhresum character, in p_cidpers character);
create or replace function pl_select.listar_estcta(in p_mhresum character, in p_cidpers character)
	returns table(
		cidpred character(10),
		ctiping character(10),
		ctiprec character(10),
		ninsolu numeric(18, 2),
		ncancel numeric(18, 2),
		cperanio character(4),
		ntipo integer,
		mpredio character(10),
		dfecadq date,
		dfecdes date,
		dfecven date,
		cmescuo character(4)
	)
as
$body$
	select a.mpredio cidpred
		, b.ctiping
		, b.ctiprec
		, sum(b.imp_insol) ninsolu
		, sum(case when estado_general::int=1 then b.imp_insol else 0.00 end) ncancel
		, b.cperanio
		, -1 ntipo
		, b.mpredio
		, a.dfecadq::date dfecadq
		, case when a.nestado='0' then a.dfecdes::date else null end dfecdes
		, b.dfecven::date dfecven
		, b.cperiod cmescuo
	from registro.dpredio a
		inner join registro.vpredio c on a.idsigma=c.dpredio
		inner join tesoreria.mestcta b on a.mpredio=b.mpredio and c.cperiod=b.cperanio
		left join public.mestados d on b.nestado = d.estado::bpchar and estado_general::int=1
	where a.mhresum=p_mhresum
		and b.cidpers=p_cidpers
		and b.ctiprec in('0000000279', '0000000283', '0000007267', '0000008509')
	group by a.idsigma, b.ctiping, b.ctiprec, b.cperanio, b.mpredio, b.cperiod, a.dfecadq::date, b.dfecven::date
	union all
	select null cidpred 
		, b.ctiping
		, b.ctiprec
		, sum(b.imp_insol) ninsolu
		, sum(case when estado_general::int=1 then b.imp_insol else 0.00 end) ncancel
		, b.cperanio
		, -1 ntipo
		, b.mpredio
		, null dfecadq
		, null dfecdes
		, b.dfecven::date dfecven
		, b.cperiod cmescuo
	from registro.dresumn a
		inner join tesoreria.mestcta b on a.cperiod=b.cperanio
		left join public.mestados d on b.nestado = d.estado::bpchar and estado_general::int=1
	where b.cidpers=p_cidpers
		and a.mhresum=p_mhresum
		and b.mhresum=p_mhresum
		and b.ctiprec='0000000273'
	group by b.ctiprec, b.ctiping, b.cperanio, b.mpredio, b.cperiod, b.dfecven::date;
$body$
language sql volatile cost 100 rows 1000;

CREATE OR REPLACE FUNCTION pl_function.generar_cuenta(p_mhresum character varying, p_mperson character varying, p_vhostnm character varying, p_vusernm character varying)
  RETURNS integer AS
$BODY$
declare
	p_idsigma integer;
	p_corigen character varying(10);
	p_mhresum_old character varying(10);
begin
	select coalesce(max(idsigma)::numeric(18, 0), 0) into p_idsigma from tesoreria.mestcta; 

	select idsigma into p_mhresum_old from registro.mhresum where mperson=p_mperson and nestado=0 order by idsigma desc limit 1;
	select ctipdat into p_corigen from registro.mhresum where idsigma=p_mhresum;
	
	insert into tesoreria.mestcta(
		  idsigma, cidpers, cidpred
		, ctiping, ctiprec, cperiod
		, ncantid, imp_insol, fact_reaj, imp_reaj
		, fact_mora, imp_mora, costo_emis
		, dfecven, dfecpag, vobserv
		, nestado, vusernm, vhostnm
		, ddatetm, cperanio, mhresum, mpredio, corigen
	) select lpad(((row_number() over(order by trim(coalesce(a.cidpred, '')))) + p_idsigma)::varchar, 10, '0') idsigma
		, $2 cidpers
		, trim(coalesce(a.cidpred, ''))
		, a.ctiping
		, a.ctiprec
		, a.cmescuo cperiod
		, 1 ncantid
		, a.ninsolu imp_insol
		, 0 fact_reaj
		, 0 imp_reaj
		, 0 fact_mora
		, 0 imp_mora
		, 0 costo_emis
		, a.dfecven
		, null dfecpag
		, '' vobserv
		, 0 nestado
		, p_vusernm vusernm
		, p_vhostnm vhostnm
		, now() ddatetm
		, a.cperanio
		, $1
		, trim(coalesce(a.mpredio, ''))
		, p_corigen corigen
	from (
		select a.cidpred,
			a.ctiping,
			a.ctiprec,
			(a.ninsolu - coalesce(b.ninsolu, 0.00)) ninsolu,
			b.ncancel,
			a.cperanio,
			a.mpredio,
			a.dfecadq,
			a.dfecdes,
			a.dfecven,
			a.cmescuo
		from pl_select.listar_estcta(p_mhresum) a
		left join pl_select.listar_estcta(p_mhresum_old, p_mperson) b on
			a.cperanio=b.cperanio and
			a.ctiprec=b.ctiprec and
			a.cmescuo=b.cmescuo and
			('0' || coalesce(a.cidpred, '0'))::int=('0' || coalesce(b.cidpred, '0'))::int
		where (a.ninsolu - coalesce(b.ninsolu, 0.00)) <> 0 and not(
			(a.ninsolu - coalesce(b.ninsolu, 0.00))  < 0 and coalesce(b.ncancel, 0) > 0
		)
	) a;

	update registro.mhresum set nrecalc=1 where idsigma=p_mhresum;
	return 1;
exception
when sqlstate '57014' then
	raise notice 'Error';
	return 0;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;